﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agenda.Entities;
using System.Data;
using System.Data.OleDb;

namespace Agenda.Repositories
{
    public class ObraRepository
    {
        private string connection = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" + DataConnection.DBConn + ";";

        public int Add(Obra obra)
        {
            int obraId = 0;

            using (OleDbConnection conn = new OleDbConnection(connection))
            {
                string statement =
                    string.Format("INSERT INTO Obra ({0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, "
                    + "{13}) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);" + "Select Scope_Identity()"
                    //, DBObjects.Obra.obraId.ToString()
                    , DBObjects.Obra.obraType.ToString()
                    , DBObjects.Obra.style.ToString()
                    , DBObjects.Obra.contractNumber.ToString()
                    , DBObjects.Obra.obraName.ToString()
                    , DBObjects.Obra.country.ToString()
                    , DBObjects.Obra.province.ToString()
                    , DBObjects.Obra.city.ToString()
                    , DBObjects.Obra.comitente.ToString()
                    , DBObjects.Obra.constructionCompany.ToString()
                    , DBObjects.Obra.subContractor.ToString()
                    , DBObjects.Obra.inspection.ToString()
                    , DBObjects.Obra.initiation.ToString()
                    , DBObjects.Obra.finish.ToString()
                    , DBObjects.Obra.amount.ToString());

                OleDbCommand command = new OleDbCommand(statement, conn);

                //command.Parameters.Add(DBObjects.Obra.obraId.ToString(), OleDbType.VarChar).Value = obra.obraId;
                command.Parameters.Add(DBObjects.Obra.obraType.ToString(), OleDbType.VarChar).Value = obra.obraType;
                command.Parameters.Add(DBObjects.Obra.style.ToString(), OleDbType.VarChar).Value = obra.style;
                command.Parameters.Add(DBObjects.Obra.contractNumber.ToString(), OleDbType.VarChar).Value = obra.contractNumber;
                command.Parameters.Add(DBObjects.Obra.obraName.ToString(), OleDbType.VarChar).Value = obra.obraName;
                command.Parameters.Add(DBObjects.Obra.country.ToString(), OleDbType.VarChar).Value = obra.country;
                command.Parameters.Add(DBObjects.Obra.province.ToString(), OleDbType.VarChar).Value = obra.province;
                command.Parameters.Add(DBObjects.Obra.city.ToString(), OleDbType.VarChar).Value = obra.city;
                command.Parameters.Add(DBObjects.Obra.comitente.ToString(), OleDbType.VarChar).Value = obra.comitente;
                command.Parameters.Add(DBObjects.Obra.constructionCompany.ToString(), OleDbType.VarChar).Value = obra.constructionCompany;
                command.Parameters.Add(DBObjects.Obra.subContractor.ToString(), OleDbType.VarChar).Value = obra.subContractor;
                command.Parameters.Add(DBObjects.Obra.inspection.ToString(), OleDbType.VarChar).Value = obra.inspection;
                command.Parameters.Add(DBObjects.Obra.initiation.ToString(), OleDbType.VarChar).Value = obra.initiation;
                command.Parameters.Add(DBObjects.Obra.finish.ToString(), OleDbType.VarChar).Value = obra.finish;
                command.Parameters.Add(DBObjects.Obra.amount.ToString(), OleDbType.VarChar).Value = obra.amount;

                DataConnection.Open(conn);

                obraId = (int)obra.obraId;
                //command.ExecuteNonQuery();

            }
            return obraId;
        }

        public Obra Get(int id)
        {
            DataTable dt = new DataTable();

            using (OleDbConnection conn = new OleDbConnection(connection))
            {
                DataSet ds = new DataSet();

                OleDbDataAdapter adapter =
                    new OleDbDataAdapter("SELECT"
                        + "*"
                        + "FROM Obra", conn);

                DataConnection.Open(conn);

                adapter.Fill(ds);

                dt = ds.Tables[0];
            }
            return MapObra(dt.Rows[0]); 
        }

        public List<Obra> ListBy(DBObjects.Obra field, string q)
        {
            List<Obra> res = new List<Obra>();

            using (OleDbConnection conn = new OleDbConnection(connection))
            {
                DataSet ds = new DataSet("Obra");

                string statement =
                    string.Format("SELECT * FROM Obra WHERE {0} = {1} ORDER BY {0}"
                    , field.ToString()
                    , q);

                OleDbDataAdapter adapter = new OleDbDataAdapter(statement, conn);

                DataConnection.Open(conn);

                adapter.Fill(ds);
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    res.Add(MapObra(dr));
                }
            }
            return res;
        }

        public void Update(Obra obra)
        {
            using (OleDbConnection conn = new OleDbConnection(connection))
            {
                string statement =
                    string.Format("UPDATE Obra ({0} = ?, {1} = ?, {2} = ?, {3} = ?, {4} = ?, {5} = ?, {6} = ?, {7} = ?, "
                    + "{8} = ?, {9} = ?, {10} = ?, {11} = ?, {12} = ?, {13} = ?, WHERE {14} = ?;)"
                    , DBObjects.Obra.obraType.ToString()
                    , DBObjects.Obra.style.ToString()
                    , DBObjects.Obra.contractNumber.ToString()
                    , DBObjects.Obra.obraName.ToString()
                    , DBObjects.Obra.country.ToString()
                    , DBObjects.Obra.province.ToString()
                    , DBObjects.Obra.city.ToString()
                    , DBObjects.Obra.comitente.ToString()
                    , DBObjects.Obra.constructionCompany.ToString()
                    , DBObjects.Obra.subContractor.ToString()
                    , DBObjects.Obra.inspection.ToString()
                    , DBObjects.Obra.initiation.ToString()
                    , DBObjects.Obra.finish.ToString()
                    , DBObjects.Obra.amount.ToString()
                    , DBObjects.Obra.obraId.ToString());


                OleDbCommand command = new OleDbCommand(statement, conn);

                command.Parameters.Add(DBObjects.Obra.obraType.ToString(), OleDbType.VarChar).Value = obra.obraType;
                command.Parameters.Add(DBObjects.Obra.style.ToString(), OleDbType.VarChar).Value = obra.style;
                command.Parameters.Add(DBObjects.Obra.contractNumber.ToString(), OleDbType.VarChar).Value = obra.contractNumber;
                command.Parameters.Add(DBObjects.Obra.obraName.ToString(), OleDbType.VarChar).Value = obra.obraName;
                command.Parameters.Add(DBObjects.Obra.country.ToString(), OleDbType.VarChar).Value = obra.country;
                command.Parameters.Add(DBObjects.Obra.province.ToString(), OleDbType.VarChar).Value = obra.province;
                command.Parameters.Add(DBObjects.Obra.city.ToString(), OleDbType.VarChar).Value = obra.city;
                command.Parameters.Add(DBObjects.Obra.comitente.ToString(), OleDbType.VarChar).Value = obra.comitente;
                command.Parameters.Add(DBObjects.Obra.constructionCompany.ToString(), OleDbType.VarChar).Value = obra.constructionCompany;
                command.Parameters.Add(DBObjects.Obra.subContractor.ToString(), OleDbType.VarChar).Value = obra.subContractor;
                command.Parameters.Add(DBObjects.Obra.inspection.ToString(), OleDbType.VarChar).Value = obra.inspection;
                command.Parameters.Add(DBObjects.Obra.initiation.ToString(), OleDbType.VarChar).Value = obra.initiation;
                command.Parameters.Add(DBObjects.Obra.finish.ToString(), OleDbType.VarChar).Value = obra.finish;
                command.Parameters.Add(DBObjects.Obra.amount.ToString(), OleDbType.VarChar).Value = obra.amount;
                command.Parameters.Add(DBObjects.Obra.obraId.ToString(), OleDbType.VarChar).Value = obra.obraId;

                DataConnection.Open(conn);

                command.ExecuteNonQuery();
            }
        }

        private Obra MapObra(DataRow dr)
        {
            Obra obra = new Obra();

            obra.obraId = Convert.ToInt32(dr[DBObjects.Obra.obraId.ToString()].ToString());
            obra.obraType = dr[DBObjects.Obra.obraType.ToString()].ToString();
            obra.style = dr[DBObjects.Obra.style.ToString()].ToString();
            obra.contractNumber = Convert.ToInt32(dr[DBObjects.Obra.contractNumber.ToString()].ToString());
            obra.obraName = dr[DBObjects.Obra.obraName.ToString()].ToString();
            obra.country = dr[DBObjects.Obra.country.ToString()].ToString();
            obra.province = dr[DBObjects.Obra.province.ToString()].ToString();
            obra.city = dr[DBObjects.Obra.city.ToString()].ToString();
            obra.comitente = dr[DBObjects.Obra.comitente.ToString()].ToString();
            obra.constructionCompany = dr[DBObjects.Obra.constructionCompany.ToString()].ToString();
            obra.subContractor = dr[DBObjects.Obra.subContractor.ToString()].ToString();
            obra.inspection = dr[DBObjects.Obra.inspection.ToString()].ToString();
            obra.initiation = Convert.ToString(dr[DBObjects.Obra.initiation.ToString()].ToString());
            obra.finish = Convert.ToString(dr[DBObjects.Obra.finish.ToString()].ToString());
            obra.amount = Convert.ToInt32(dr[DBObjects.Obra.amount.ToString()].ToString());

            return obra;
        }
    }
}
